package cn.stylefeng.guns.modular.tool.service.impl;

import java.io.InputStream;
import java.io.Serializable;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;

import cn.stylefeng.guns.base.pojo.page.LayuiPageFactory;
import cn.stylefeng.guns.base.pojo.page.LayuiPageInfo;
import cn.stylefeng.guns.core.util.ExcelWrite;
import cn.stylefeng.guns.modular.tool.entity.ToolExpressReconciliationZt;
import cn.stylefeng.guns.modular.tool.mapper.ToolExpressReconciliationZtMapper;
import cn.stylefeng.guns.modular.tool.model.params.ToolExpressReconciliationFwParam;
import cn.stylefeng.guns.modular.tool.model.params.ToolExpressReconciliationRfParam;
import cn.stylefeng.guns.modular.tool.model.params.ToolExpressReconciliationYfInstallParam;
import cn.stylefeng.guns.modular.tool.model.params.ToolExpressReconciliationZtInstallParam;
import cn.stylefeng.guns.modular.tool.model.params.ToolExpressReconciliationZtParam;
import cn.stylefeng.guns.modular.tool.model.result.ToolExpressReconciliationYfInstallResult;
import cn.stylefeng.guns.modular.tool.model.result.ToolExpressReconciliationZtInstallResult;
import cn.stylefeng.guns.modular.tool.model.result.ToolExpressReconciliationZtResult;
import cn.stylefeng.guns.modular.tool.model.result.orderCount;
import cn.stylefeng.guns.modular.tool.service.ToolExpressReconciliationRfService;
import cn.stylefeng.guns.modular.tool.service.ToolExpressReconciliationYfInstallService;
import cn.stylefeng.guns.modular.tool.service.ToolExpressReconciliationZtInstallService;
import  cn.stylefeng.guns.modular.tool.service.ToolExpressReconciliationZtService;
import cn.stylefeng.guns.sys.modular.system.entity.User;
import cn.stylefeng.guns.sys.modular.system.service.UserService;
import cn.stylefeng.roses.core.reqres.response.ResponseData;
import cn.stylefeng.roses.core.util.ToolUtil;

/**
 * <p>
 * 中通对账管理 服务实现类
 * </p>
 * @author zhucx
 * @since 2023-01-30
 */
@Service
public class ToolExpressReconciliationZtServiceImpl extends ServiceImpl<ToolExpressReconciliationZtMapper, ToolExpressReconciliationZt> implements ToolExpressReconciliationZtService {

    @Override
    public void add(ToolExpressReconciliationZtParam param){
        ToolExpressReconciliationZt entity = getEntity(param);
        this.save(entity);
    }

    @Override
    public void delete(ToolExpressReconciliationZtParam param){
        this.removeById(getKey(param));
    }

    @Override
    public void update(ToolExpressReconciliationZtParam param){
        ToolExpressReconciliationZt oldEntity = getOldEntity(param);
        ToolExpressReconciliationZt newEntity = getEntity(param);
        ToolUtil.copyProperties(newEntity, oldEntity);
        this.updateById(newEntity);
    }

    @Override
    public ToolExpressReconciliationZtResult findBySpec(ToolExpressReconciliationZtParam param){
        return null;
    }

    @Override
    public List<ToolExpressReconciliationZtResult> findListBySpec(ToolExpressReconciliationZtParam param){
        return this.baseMapper.findListBySpec(param);
    }

    @Override
    public LayuiPageInfo findPageBySpec(ToolExpressReconciliationZtParam param,String condition,Long operateId){
        Page pageContext = getPageContext();
        IPage page = this.baseMapper.customPageList(pageContext, param,condition, operateId);
        return LayuiPageFactory.createPageInfo(page);
    }

    @Override
    public LayuiPageInfo findPageBySpecDz(ToolExpressReconciliationZtParam param,String condition){
        Page pageContext = getPageContext();
        IPage page = this.baseMapper.findPageBySpecDz(pageContext, param,condition);
        return LayuiPageFactory.createPageInfo(page);
    }

    private Serializable getKey(ToolExpressReconciliationZtParam param){
        return param.getId();
    }

    private Page getPageContext() {
        return LayuiPageFactory.defaultPage();
    }

    private ToolExpressReconciliationZt getOldEntity(ToolExpressReconciliationZtParam param) {
        return this.getById(getKey(param));
    }

    private ToolExpressReconciliationZt getEntity(ToolExpressReconciliationZtParam param) {
        ToolExpressReconciliationZt entity = new ToolExpressReconciliationZt();
        ToolUtil.copyProperties(param, entity);
        return entity;
    }
    
    @Resource
    private UserService userService;

    @Resource
    private ToolExpressReconciliationZtInstallService toolExpressReconciliationZtInstallService;
    
    @Resource
    private ToolExpressReconciliationRfService toolExpressReconciliationRfService;

    @Resource
    private ToolExpressReconciliationYfInstallService toolExpressReconciliationYfInstallService;

	@Override
	public ResponseData getExceldata(MultipartFile file, Long reconciliationId, ResponseData data, String zfahuonum, String daynum) throws Exception {
		String fileName = file.getOriginalFilename();
		
		User user = userService.getcurrentUser();

		BigDecimal zfahuonumbig = new BigDecimal(zfahuonum);//总发货量
		BigDecimal daynumbig = new BigDecimal(daynum);//发货天数
		BigDecimal rijunbig = zfahuonumbig.divide(daynumbig,2,BigDecimal.ROUND_HALF_DOWN);//日均发货量  =   总发货量    /    发货天数
		
		BigDecimal fu2 = new BigDecimal("-2");
    	BigDecimal as = new BigDecimal("0");
    	BigDecimal qj05 = new BigDecimal("0.5");
    	BigDecimal yi = new BigDecimal("1");
    	BigDecimal er = new BigDecimal("2");
    	BigDecimal san = new BigDecimal("3");
    	BigDecimal wudwu = new BigDecimal("5.5");
    	
    	BigDecimal wubai = new BigDecimal("500");
    	BigDecimal yiq = new BigDecimal("1000");
    	BigDecimal erqian = new BigDecimal("2000");
    	BigDecimal sanqian = new BigDecimal("3000");
		
		List<ToolExpressReconciliationZtParam> ztlist = new ArrayList<ToolExpressReconciliationZtParam>();//中通对账单
		
		List<ToolExpressReconciliationRfParam> rflist = new ArrayList<ToolExpressReconciliationRfParam>();//软风对账单
        
        ToolExpressReconciliationYfInstallParam expressReconciliationYfInstallParam = new ToolExpressReconciliationYfInstallParam();
        expressReconciliationYfInstallParam.setDeptId(user.getDeptId());
        List<ToolExpressReconciliationYfInstallResult> rfi = toolExpressReconciliationYfInstallService.findListBySpec(expressReconciliationYfInstallParam,user.getOperateId());//软风报价
        
        ToolExpressReconciliationZtInstallParam expressReconciliationZtInstallParam = new ToolExpressReconciliationZtInstallParam();
        expressReconciliationZtInstallParam.setDeptId(user.getDeptId());
        List<ToolExpressReconciliationZtInstallResult> zti = toolExpressReconciliationZtInstallService.findListBySpec(expressReconciliationZtInstallParam,user.getOperateId());//中通报价
		
		if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
			data.setMessage("上传文件格式不正确");
			data.setSuccess(false);
			return data;
        }

		boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        InputStream is = file.getInputStream();
        Workbook wb = null;
        if (isExcel2003) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }
        Sheet sheet = wb.getSheetAt(0);

        for (int r = 1; r <= sheet.getLastRowNum()+1; r++) {
        	Row row = sheet.getRow(r);
            if (row == null){
                continue;
            }
            
            ToolExpressReconciliationZtParam ztParam = new ToolExpressReconciliationZtParam();//中通账单
            ztParam.setReconciliationId(reconciliationId);
            
            ToolExpressReconciliationRfParam rfffParam = new ToolExpressReconciliationRfParam();//软风账单
            rfffParam.setV1("zt");
            rfffParam.setReconciliationId(reconciliationId);
        
            String  orderNumber = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(0));//运单号
            if (ToolUtil.isNotEmpty(orderNumber)) {
            	ztParam.setOrderNumber(orderNumber);
            	rfffParam.setOrderNumber(orderNumber);
			} else {
				break;
			}
        
            String  zhangdanriqi = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(1));//账单日期
            if (ToolUtil.isNotEmpty(zhangdanriqi)) {
            	ztParam.setZhangdanriqi(zhangdanriqi);
			}
        
            String  kehumingcheng = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(2));//客户名称
            if (ToolUtil.isNotEmpty(kehumingcheng)) {
            	ztParam.setKehumingcheng(kehumingcheng);
			}
        
            String  jiesuanduixiang = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(3));//结算对象
            if (ToolUtil.isNotEmpty(jiesuanduixiang)) {
            	ztParam.setJiesuanduixiang(jiesuanduixiang);
			}
        
            String  mddProvince = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(4));//目的地省
            if (ToolUtil.isNotEmpty(mddProvince)) {
            	ztParam.setProvince(mddProvince.replaceAll("省", "").replaceAll("市", "").replaceAll("壮族自治区", "").replaceAll("回族自治区", "").replaceAll("维吾尔自治区", "").replaceAll("自治区", ""));//省份
            	rfffParam.setProvince(mddProvince.replaceAll("省", "").replaceAll("市", "").replaceAll("壮族自治区", "").replaceAll("回族自治区", "").replaceAll("维吾尔自治区", "").replaceAll("自治区", ""));//省份
            	ztParam.setMddProvince(mddProvince);
			}
        
            String  ycProvince = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(21));//省
            if (ToolUtil.isNotEmpty(ycProvince)) {
            	ztParam.setYcProvince(ycProvince.replaceAll("省", "").replaceAll("市", "").replaceAll("壮族自治区", "").replaceAll("回族自治区", "").replaceAll("维吾尔自治区", "").replaceAll("自治区", ""));//省份
            	rfffParam.setProvinces(ycProvince);
            	if(ToolUtil.isNotEmpty(ztParam.getProvince())) {
    				if(ztParam.getProvince().equals(ztParam.getYcProvince())) {
    					ztParam.setProvinceTrue("true");
    				} else {
    					ztParam.setProvinceTrue("false");
    				}
    			}
			}
        
            String  mddCity = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(5));//目的地市
            if (ToolUtil.isNotEmpty(mddCity)) {
            	ztParam.setMddCity(mddCity);
    			if(mddCity.equals("深圳市")||mddCity.equals("深圳")) {
    				rfffParam.setRemarks("深圳");
    			}
			}
        
            String  settlementWeight = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(6));//中心结算重量
            if (ToolUtil.isNotEmpty(settlementWeight)) {
    			BigDecimal settlementWeightbig = new BigDecimal(settlementWeight).setScale(2, BigDecimal.ROUND_HALF_UP);
            	ztParam.setSettlementWeight(settlementWeightbig.toString());
            	ztParam.setRoundingWeightKg(settlementWeightbig.setScale( 0, BigDecimal.ROUND_UP ).longValue()+"");//取整重量

            	rfffParam.setChargingWeightKg(settlementWeightbig.toString());
            	rfffParam.setRoundingWeightKg(settlementWeightbig.setScale( 0, BigDecimal.ROUND_UP ).longValue()+"");//取整重量
			}
            
            BigDecimal settlementExpensesSumbig = new BigDecimal("0");
        
            String  settlementExpenses = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(7));//应收费用
            if (ToolUtil.isNotEmpty(settlementExpenses)) {
    			BigDecimal settlementExpensesbig = new BigDecimal(settlementExpenses).setScale(2, BigDecimal.ROUND_HALF_UP);
            	ztParam.setSettlementExpenses(settlementExpensesbig.toString());
            	settlementExpensesSumbig = settlementExpensesbig;
			}
        
            String  wangjijiashou = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(8));//旺季加收
            if (ToolUtil.isNotEmpty(wangjijiashou)) {
    			BigDecimal wangjijiashoubig = new BigDecimal(wangjijiashou).setScale(2, BigDecimal.ROUND_HALF_UP);
            	ztParam.setWangjijiashou(wangjijiashoubig.toString());
            	ztParam.setWangjijiashou2(wangjijiashoubig.toString());
            	settlementExpensesSumbig = settlementExpensesSumbig.add(wangjijiashoubig).setScale(2, BigDecimal.ROUND_HALF_UP);
			}
        
            String  additional = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(9));//疫情调价
            if (ToolUtil.isNotEmpty(additional)) {
    			BigDecimal additionalbig = new BigDecimal(additional).setScale(2, BigDecimal.ROUND_HALF_UP);
            	ztParam.setAdditional(additionalbig.toString());
            	ztParam.setAdditional2(additionalbig.toString());
            	settlementExpensesSumbig = settlementExpensesSumbig.add(additionalbig).setScale(2, BigDecimal.ROUND_HALF_UP);
			}
            
            ztParam.setSettlementExpensesSum(settlementExpensesSumbig.toString());//总运费
            
            String  ycWeight = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(10));//云仓重量
            if (ToolUtil.isNotEmpty(ycWeight)) {
    			BigDecimal ycWeightbig = new BigDecimal(ycWeight).setScale(3, BigDecimal.ROUND_HALF_UP);
    			ztParam.setYcWeight(ycWeightbig.toString());
    			
    			if(ToolUtil.isNotEmpty(ztParam.getSettlementWeight())) {
    				BigDecimal settlementWeightbig = new BigDecimal(ztParam.getSettlementWeight()).setScale(2,BigDecimal.ROUND_HALF_UP);
    				if(ycWeightbig.compareTo(settlementWeightbig) == 1) {
    					rfffParam.setChargingWeightKg(ycWeightbig.toString());
    					rfffParam.setRoundingWeightKg(ycWeightbig.setScale( 0, BigDecimal.ROUND_UP ).longValue()+"");//取整重量
    				}
                	
    				BigDecimal weightChayi = settlementWeightbig.subtract(ycWeightbig).setScale(3,BigDecimal.ROUND_HALF_UP);
    				ztParam.setWeightChayi(weightChayi.toString());//重量差异
    			}
            }
            
            if(ToolUtil.isNotEmpty(ztParam.getSettlementWeight())) {
    			BigDecimal settlementWeightbig = new BigDecimal(settlementWeight).setScale(2, BigDecimal.ROUND_HALF_UP);//中心结算重量
                if(ToolUtil.isNotEmpty(ztParam.getProvince())) {
            		//匹配中通报价   计算核对邮费  计算邮费差异
            		String provincestr = ztParam.getProvince();
            		if(ToolUtil.isNotEmpty(ztParam.getMddCity())) {
            			if(ztParam.getMddCity().indexOf("凉山") == 0) {
            				provincestr = "四川凉山";
            			}
            			if(ztParam.getMddCity().indexOf("甘孜") == 0) {
            				provincestr = "四川甘孜";
            			}
            			if(ztParam.getMddCity().indexOf("阿坝") == 0) {
            				provincestr = "四川阿坝";
            			}
            			if(ztParam.getMddCity().indexOf("包头") == 0) {
            				provincestr = "内蒙包头中心";
            			}
            		} 
            		boolean flag = false;
                	for (ToolExpressReconciliationZtInstallResult i : zti) {
						if(provincestr.equals(i.getProvince())) {
        					if(settlementWeightbig.compareTo(as) == 1 && settlementWeightbig.compareTo(qj05) != 1) {//0 -  500g
        						ztParam.setWeightQj("0-0.5kg");
        						if(rijunbig.compareTo(wubai) == 1 && rijunbig.compareTo(yiq) != 1) {//5百-1千单
        							if(ToolUtil.isNotEmpty(i.getQj05g51())) {
        								ztParam.setPostageCheck(i.getQj05g51());
        								flag = true;
        							}
        							break;
        						}
        						if(rijunbig.compareTo(yiq) == 1 && rijunbig.compareTo(erqian) != 1) {//1千-2千单
        							if(ToolUtil.isNotEmpty(i.getQj05g12())) {
        								ztParam.setPostageCheck(i.getQj05g12());
        								flag = true;
        							}
        							break;
        						}
        						if(rijunbig.compareTo(erqian) == 1 && rijunbig.compareTo(sanqian) != 1) {//2千-3千单
        							if(ToolUtil.isNotEmpty(i.getQj05g23())) {
        								ztParam.setPostageCheck(i.getQj05g23());
        								flag = true;
        							}
        							break;
        						}
        						if(rijunbig.compareTo(sanqian) == 1) {//3千单以上
        							if(ToolUtil.isNotEmpty(i.getQj05g3())) {
        								ztParam.setPostageCheck(i.getQj05g3());
        								flag = true;
        							}
        							break;
        						}
    							break;
        					}
        					if(settlementWeightbig.compareTo(qj05) == 1 && settlementWeightbig.compareTo(yi) != 1) {//500-1kg
        						ztParam.setWeightQj("0.5-1kg");
        						if(rijunbig.compareTo(wubai) == 1 && rijunbig.compareTo(yiq) != 1) {//5百-1千单
        							if(ToolUtil.isNotEmpty(i.getQj51kg51())) {
        								ztParam.setPostageCheck(i.getQj51kg51());
        								flag = true;
        							}
        							break;
        						}
        						if(rijunbig.compareTo(yiq) == 1 && rijunbig.compareTo(erqian) != 1) {//1千-2千单
        							if(ToolUtil.isNotEmpty(i.getQj51kg12())) {
        								ztParam.setPostageCheck(i.getQj51kg12());
        								flag = true;
        							}
        							break;
        						}
        						if(rijunbig.compareTo(erqian) == 1 && rijunbig.compareTo(sanqian) != 1) {//2千-3千单
        							if(ToolUtil.isNotEmpty(i.getQj51kg23())) {
        								ztParam.setPostageCheck(i.getQj51kg23());
        								flag = true;
        							}
        							break;
        						}
        						if(rijunbig.compareTo(sanqian) == 1) {//3千单以上
        							if(ToolUtil.isNotEmpty(i.getQj51kg3())) {
        								ztParam.setPostageCheck(i.getQj51kg3());
        								flag = true;
        							}
        							break;
        						}
        						break;
        					}
        					if(settlementWeightbig.compareTo(yi) == 1 && settlementWeightbig.compareTo(er) != 1) {//1kg-2kg
        						ztParam.setWeightQj("1-2kg");
        						if(rijunbig.compareTo(wubai) == 1 && rijunbig.compareTo(yiq) != 1) {//5百-1千单
        							if(ToolUtil.isNotEmpty(i.getQj12kg51())) {
        								ztParam.setPostageCheck(i.getQj12kg51());
        								flag = true;
        							}
        							break;
        						}
        						if(rijunbig.compareTo(yiq) == 1 && rijunbig.compareTo(erqian) != 1) {//1千-2千单
        							if(ToolUtil.isNotEmpty(i.getQj12kg12())) {
        								ztParam.setPostageCheck(i.getQj12kg12());
        								flag = true;
        							}
        							break;
        						}
        						if(rijunbig.compareTo(erqian) == 1 && rijunbig.compareTo(sanqian) != 1) {//2千-3千单
        							if(ToolUtil.isNotEmpty(i.getQj12kg23())) {
        								ztParam.setPostageCheck(i.getQj12kg23());
        								flag = true;
        							}
        							break;
        						}
        						if(rijunbig.compareTo(sanqian) == 1) {//3千单以上
        							if(ToolUtil.isNotEmpty(i.getQj12kg3())) {
        								ztParam.setPostageCheck(i.getQj12kg3());
        								flag = true;
        							}
        							break;
        						}
        						break;
        					}
        					if(settlementWeightbig.compareTo(er) == 1 && settlementWeightbig.compareTo(san) != 1) {//2kg-3kg
        						ztParam.setWeightQj("2-3kg");
        						if(rijunbig.compareTo(wubai) == 1 && rijunbig.compareTo(yiq) != 1) {//5百-1千单
        							if(ToolUtil.isNotEmpty(i.getQj23kg51())) {
        								ztParam.setPostageCheck(i.getQj23kg51());
        								flag = true;
        							}
        							break;
        						}
        						if(rijunbig.compareTo(yiq) == 1 && rijunbig.compareTo(erqian) != 1) {//1千-2千单
        							if(ToolUtil.isNotEmpty(i.getQj23kg12())) {
        								ztParam.setPostageCheck(i.getQj23kg12());
        								flag = true;
        							}
        							break;
        						}
        						if(rijunbig.compareTo(erqian) == 1 && rijunbig.compareTo(sanqian) != 1) {//2千-3千单
        							if(ToolUtil.isNotEmpty(i.getQj23kg23())) {
        								ztParam.setPostageCheck(i.getQj23kg23());
        								flag = true;
        							}
        							break;
        						}
        						if(rijunbig.compareTo(sanqian) == 1) {//3千单以上
        							if(ToolUtil.isNotEmpty(i.getQj23kg3())) {
        								ztParam.setPostageCheck(i.getQj23kg3());
        								flag = true;
        							}
        							break;
        						}
        						break;
        					}
    						break;
						}
					}
                	if (flag) {
						BigDecimal postageCheckSumbig = new BigDecimal("0");//总快递费 =   核对快递费 + 旺季加收 + 疫情调价
						BigDecimal postageCheckbig = new BigDecimal(ztParam.getPostageCheck());
						postageCheckSumbig = postageCheckSumbig.add(postageCheckbig).setScale(1, BigDecimal.ROUND_HALF_UP);//+核对快递费
						if(ToolUtil.isNotEmpty(ztParam.getWangjijiashou2())) {
			    			BigDecimal wangjijiashou2big = new BigDecimal(ztParam.getWangjijiashou2()).setScale(2, BigDecimal.ROUND_HALF_UP);
			    			postageCheckSumbig = postageCheckSumbig.add(wangjijiashou2big).setScale(2, BigDecimal.ROUND_HALF_UP);//+ 旺季加收
						}
						if(ToolUtil.isNotEmpty(ztParam.getAdditional2())) {
							BigDecimal additional2big = new BigDecimal(ztParam.getAdditional2());
							postageCheckSumbig = postageCheckSumbig.add(additional2big).setScale(2, BigDecimal.ROUND_HALF_UP);// + 疫情加收
						}
						ztParam.setPostageCheckSum(postageCheckSumbig.toString());
						
						if(ToolUtil.isNotEmpty(ztParam.getSettlementExpensesSum())) {//运费差异 = 总运费  - 核对总快递费
							BigDecimal postageDifference = settlementExpensesSumbig.subtract(postageCheckSumbig).setScale(2, BigDecimal.ROUND_HALF_UP);
							ztParam.setPostageDifference(postageDifference.toString());
						}
					}
                }
            }

            String  customerName = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(11));//客户
            if (ToolUtil.isNotEmpty(customerName)) {
            	ztParam.setCustomerName(customerName);
			}

            String  outNumber = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(12));//出库单号
            if (ToolUtil.isNotEmpty(outNumber)) {
            	rfffParam.setOutNumber(outNumber);
			}

            String  shopNumber = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(13));//店铺编号
            if (ToolUtil.isNotEmpty(shopNumber)) {
            	rfffParam.setShopNumber(shopNumber);
			}
            
            String  shopName = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(14));//店铺名称
            if (ToolUtil.isNotEmpty(shopName)) {
            	rfffParam.setShopName(shopName);
			}
            
            String  platform = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(15));//平台站点
            if (ToolUtil.isNotEmpty(platform)) {
            	rfffParam.setPlatform(platform);
			}
            
            String  outWNumber = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(16));//出仓单号
            if (ToolUtil.isNotEmpty(outWNumber)) {
            	rfffParam.setOutWNumber(outWNumber);
			}
            
            String  inOrderNumber = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(17));//内部订单号
            if (ToolUtil.isNotEmpty(inOrderNumber)) {
            	rfffParam.setInOrderNumber(inOrderNumber);
			}
            
            String  outTime = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(18));//出库日期
            if (ToolUtil.isNotEmpty(outTime)) {
            	rfffParam.setOutTime(outTime);
			}
            
            String  states = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(19));//状态
            if (ToolUtil.isNotEmpty(states)) {
            	rfffParam.setStates(states);
			}
            
            String  expressName = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(20));//快递公司
            if (ToolUtil.isNotEmpty(expressName)) {
        		rfffParam.setExpressName(expressName);
            }
            
            String  city = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(22));//市
            if (ToolUtil.isNotEmpty(city)) {
        		rfffParam.setCity(city);
            }
            
            String  area = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(23));//区
            if (ToolUtil.isNotEmpty(area)) {
        		rfffParam.setArea(area);
    			if(area.equals("虎门镇")||area.equals("虎门港管委会")) {
    				rfffParam.setRemarks("虎门");
    			}
            }
            
            String  street = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(24));//街道
            if (ToolUtil.isNotEmpty(street)) {
        		rfffParam.setStreet(street);
    			if(street.equals("虎门镇")) {
    				rfffParam.setRemarks("虎门");
    			}
            }
            
            String  nums = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(25));//发货件数
            if (ToolUtil.isNotEmpty(nums)) {
        		rfffParam.setNums(nums);
        		ztParam.setQuantity(nums);
            }

            if(ToolUtil.isNotEmpty(rfffParam.getChargingWeightKg())) {
            	BigDecimal chargingWeightKgbig = new BigDecimal(rfffParam.getChargingWeightKg()).setScale(3, BigDecimal.ROUND_HALF_UP);
                if(ToolUtil.isNotEmpty(rfffParam.getProvince())) {
            		//匹配软风报价   计算快递费，邮政账单收入、利润
            		BigDecimal sumExpressMoney = new BigDecimal("0");//总快递费
            		String provincestr = "";
            		if(ToolUtil.isNotEmpty(rfffParam.getRemarks())) {
            			provincestr = rfffParam.getRemarks();
            		} else {
            			provincestr = rfffParam.getProvince();
            		}
            		for (ToolExpressReconciliationYfInstallResult i : rfi) {
        				if(provincestr.equals(i.getDestination())) {
        					if(provincestr.equals("海南")||provincestr.equals("新疆")||provincestr.equals("西藏")) {
            					if(chargingWeightKgbig.compareTo(as) == 1 && chargingWeightKgbig.compareTo(yi) != 1) {//0 - 1kg
            						ztParam.setWeightQj2("1KG以下");
            					}
            					if(chargingWeightKgbig.compareTo(yi) == 1 && chargingWeightKgbig.compareTo(er) != 1) {//1 - 2kg
            						ztParam.setWeightQj2("1-2KG");
            					}
            					if(chargingWeightKgbig.compareTo(er) == 1 && chargingWeightKgbig.compareTo(san) != 1) {//2 - 3kg
            						ztParam.setWeightQj2("2-3KG");
            					}
            					if(chargingWeightKgbig.compareTo(san) == 1) {//3kg以上
            						ztParam.setWeightQj2("3KG以上");
            					}
        						if(ToolUtil.isNotEmpty(i.getUnilateralism()) && ToolUtil.isNotEmpty(i.getCWeight())) {
        							BigDecimal unilateralism = new BigDecimal(i.getUnilateralism());//面单费
        							BigDecimal cCWeight = new BigDecimal(i.getCWeight());//续重
        							BigDecimal wmulcCWeight = chargingWeightKgbig.multiply(cCWeight);//重量*续重
        							BigDecimal expressMoney = unilateralism.add(wmulcCWeight).setScale(3, BigDecimal.ROUND_HALF_UP);//面单费+（重量*续重）
        							sumExpressMoney = expressMoney;
        							rfffParam.setUnilateralism(unilateralism.toString());
        							rfffParam.setCWeight(cCWeight.toString());
        							rfffParam.setExpressMoney(expressMoney.toString());//快递费
        						}
        					} else {
            					if(chargingWeightKgbig.compareTo(as) == 1 && chargingWeightKgbig.compareTo(yi) != 1) {//0 - 1kg
            						ztParam.setWeightQj2("1KG以下");
            						if(ToolUtil.isNotEmpty(i.getQj01kg())) {
        								rfffParam.setExpressMoney(i.getQj01kg());//快递费
        								BigDecimal qj01kg = new BigDecimal(i.getQj01kg());
        								sumExpressMoney = qj01kg;
            						}
            					}
            					if(chargingWeightKgbig.compareTo(yi) == 1 && chargingWeightKgbig.compareTo(er) != 1) {//1 - 2kg
            						ztParam.setWeightQj2("1-2KG");
            						if(ToolUtil.isNotEmpty(i.getQj12kg())) {
        								rfffParam.setExpressMoney(i.getQj12kg());//快递费
        								BigDecimal qj12kg = new BigDecimal(i.getQj12kg());
        								sumExpressMoney = qj12kg;
            						}
            					}
            					if(chargingWeightKgbig.compareTo(er) == 1 && chargingWeightKgbig.compareTo(san) != 1) {//2 - 3kg
            						ztParam.setWeightQj2("2-3KG");
            						if(ToolUtil.isNotEmpty(i.getQj23kg())) {
        								rfffParam.setExpressMoney(i.getQj23kg());//快递费
        								BigDecimal qj23kg = new BigDecimal(i.getQj23kg());
        								sumExpressMoney = qj23kg;
            						}
            					}
            					if(chargingWeightKgbig.compareTo(san) == 1) {//3kg以上
            						ztParam.setWeightQj2("3KG以上");
        							if(ToolUtil.isNotEmpty(i.getUnilateralism()) && ToolUtil.isNotEmpty(i.getCWeight())) {
        								BigDecimal unilateralism = new BigDecimal(i.getUnilateralism());//面单费
        								BigDecimal cCWeight = new BigDecimal(i.getCWeight());//续重
        								BigDecimal wmulcCWeight = chargingWeightKgbig.multiply(cCWeight);//重量*续重
        								BigDecimal expressMoney = unilateralism.add(wmulcCWeight).setScale(3, BigDecimal.ROUND_HALF_UP);//面单费+（重量*续重）
        								sumExpressMoney = expressMoney;
        								rfffParam.setUnilateralism(unilateralism.toString());
        								rfffParam.setCWeight(cCWeight.toString());
        								rfffParam.setExpressMoney(expressMoney.toString());//快递费
        							}
            					}
        					}
        					if(ToolUtil.isNotEmpty(i.getAdditional())) {//地区加收
        						BigDecimal additional2 = new BigDecimal(i.getAdditional());
        						BigDecimal wmuladditional = chargingWeightKgbig.multiply(additional2).setScale(4, BigDecimal.ROUND_HALF_UP);//重量*地区加收
        						rfffParam.setAdditional(wmuladditional.toString());
        						sumExpressMoney = sumExpressMoney.add(wmuladditional);
        					}
        					if(ToolUtil.isNotEmpty(i.getStandard()) && ToolUtil.isNotEmpty(i.getMinimum())) {//中转费
        						rfffParam.setStandard(i.getStandard());
        						rfffParam.setMinimum(i.getMinimum());
        						BigDecimal standard = new BigDecimal(i.getStandard());//二三级中转标准
        						BigDecimal minimum = new BigDecimal(i.getMinimum());//二三级中转最低
        						BigDecimal wmulstandard = standard.multiply(chargingWeightKgbig).setScale(4, BigDecimal.ROUND_HALF_UP);//二三级中转标准*重量
        						//如果  二三级中转标准*重量 > 二三级中转最低 就用二三级中转标准*重量 作为中转费，不然就用二三级中转最低作为中转费
        						if(wmulstandard.compareTo(minimum) == 1) {
        							rfffParam.setTransferMoney(wmulstandard.toString());
        							sumExpressMoney = sumExpressMoney.add(wmulstandard);
        						} else {
        							rfffParam.setTransferMoney(i.getMinimum());
        							sumExpressMoney = sumExpressMoney.add(minimum);
        						}
        					}
        					if(chargingWeightKgbig.compareTo(wudwu) == 1) {//5.5kg以上
        						if(ToolUtil.isNotEmpty(i.getZz55above())) {//5.5Kg以上的中转费
        							BigDecimal zz55above = new BigDecimal(i.getZz55above());
        							BigDecimal wmulzz55above = zz55above.multiply(chargingWeightKgbig).setScale(4, BigDecimal.ROUND_HALF_UP);
        							rfffParam.setTransferMoney(wmulzz55above.toString());
        							sumExpressMoney = sumExpressMoney.add(wmulzz55above);
        						}
        					}
        					rfffParam.setSumExpressMoney(sumExpressMoney.setScale(2, BigDecimal.ROUND_HALF_UP).toString());//总快递费
        					ztParam.setIncome(sumExpressMoney.setScale(2, BigDecimal.ROUND_HALF_UP).toString());//收入
        					break;
        				}
        			}
                }
            }

            if(ToolUtil.isNotEmpty(ztParam.getSettlementExpensesSum()) && ToolUtil.isNotEmpty(rfffParam.getSumExpressMoney())) {
            	BigDecimal sumExpressMoney = new BigDecimal(rfffParam.getSumExpressMoney());//总快递费
            	BigDecimal profit = sumExpressMoney.subtract(settlementExpensesSumbig).setScale(2,BigDecimal.ROUND_HALF_UP);//总快递费  - 总运费 = 利润
            	ztParam.setProfit(profit.toString());//利润
            }
            
            ztParam.setAdvanceCharge("-2");//预付款
            
            if(ToolUtil.isNotEmpty(ztParam.getSettlementExpensesSum())) {//应付  =  总运费 + 预付款
				BigDecimal copeWithbig = settlementExpensesSumbig.add(fu2).setScale(2, BigDecimal.ROUND_HALF_UP);
				ztParam.setCopeWith(copeWithbig.toString());
            }
            
            ztlist.add(ztParam);
            
            if(ToolUtil.isNotEmpty(customerName)) {
            	if(customerName.equals("软风2C")) {
            		rflist.add(rfffParam);
            	}
            }
        }
        if(ztlist.size() > 0) {
        	this.saveBatchList(ztlist);
        }
        if(rflist.size() > 0) {
        	toolExpressReconciliationRfService.saveBatchList(rflist);
        }
		return ResponseData.success();
	}

	@Override
	public Workbook exportToExcel(Long reconciliationId) {
	    //这是表头
	    String[] arr = {"运单号","账单日期","客户名称","结算对象","省份","云仓省份","省份差异","目的地省","目的地市","中心结算重量","应收费用","旺季加收","疫情调价","总运费"
	    		,"重量区间（快递）","云仓重量","重量差异","取整重量","核对快递费","首重","续重","旺季加收","疫情调价","总快递费","运费差异"
	    		,"客户","收入","利润","重量区间","数量","预付款","应付","备注"};
	    //这是具体数据
	    List<List<String>> list = new ArrayList<>();
	    
	    ToolExpressReconciliationZtParam param = new ToolExpressReconciliationZtParam();
	    param.setReconciliationId(reconciliationId);
	    List<ToolExpressReconciliationZtResult> datalist = this.findListBySpec(param);
	    for (ToolExpressReconciliationZtResult r : datalist) {
		    List<String> list1 = new ArrayList<String>();
		    list1.add(r.getOrderNumber());
		    list1.add(r.getZhangdanriqi());
		    list1.add(r.getKehumingcheng());
		    list1.add(r.getJiesuanduixiang());
		    list1.add(r.getProvince());
		    list1.add(r.getYcProvince());
		    list1.add(r.getProvinceTrue());
		    list1.add(r.getMddProvince());
		    list1.add(r.getMddCity());
		    list1.add(r.getSettlementWeight());
		    list1.add(r.getSettlementExpenses());
		    list1.add(r.getWangjijiashou());
		    list1.add(r.getAdditional());
		    list1.add(r.getSettlementExpensesSum());
		    list1.add(r.getWeightQj());
		    list1.add(r.getYcWeight());
		    list1.add(r.getWeightChayi());
		    list1.add(r.getRoundingWeightKg());
		    list1.add(r.getPostageCheck());
		    list1.add(r.getFirstWeight());
		    list1.add(r.getWeightContinuation());
		    list1.add(r.getWangjijiashou2());
		    list1.add(r.getAdditional2());
		    list1.add(r.getPostageCheckSum());
		    list1.add(r.getPostageDifference());
		    list1.add(r.getCustomerName());
		    list1.add(r.getIncome());
		    list1.add(r.getProfit());
		    list1.add(r.getWeightQj2());
		    list1.add(r.getQuantity());
		    list1.add(r.getAdvanceCharge());
		    list1.add(r.getCopeWith());
		    list1.add(r.getRemarks());
		    list.add(list1);
		}
	    return ExcelWrite.writeToExcelByList(arr, list);
	}

	@Override
	public void deleteBySpec(ToolExpressReconciliationZtParam param) {
		this.baseMapper.deleteBySpec(param);
	}

	@Override
	public boolean saveBatchList(List<ToolExpressReconciliationZtParam> list) {
		return this.baseMapper.saveBatchList(list);
	}

	@Override
	public List<orderCount> findorderCount(ToolExpressReconciliationZtParam param, Long operateId) {
		return this.baseMapper.findorderCount(param,operateId);
	}

}
